package com.sharecar.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.android.sharecar.model.CommentLevel1;
import com.android.sharecar.model.CommentLevel2;
import com.android.sharecar.model.User;
import com.sharecar.db.DB;

public class CommentLevel2Service {
	private static CommentLevel2Service service;

	private CommentLevel2Service() {
	}

	public static CommentLevel2Service getInstance() {
		if (null == service) {
			service = new CommentLevel2Service();
		}
		return service;
	}

	public void addComment(CommentLevel1 comment) {
		Connection conn = DB.getConn();
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

		String sql = "insert into SecondLevelComments values (null, ?, ?, ?, ?)";
		PreparedStatement pstmt = DB.prepare(conn, sql);
		try {
			pstmt.setString(1, comment.getContent());
			pstmt.setTimestamp(2,
					new Timestamp(comment.getPostTime().getTime()));

			// If here cause exception, then set it to 0..
			pstmt.setInt(3, comment.getTalkId());
			pstmt.setInt(4, comment.getUser().getUserId());
			pstmt.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	/**
	 * Get the second level comments of specific first level comment
	 *  by its id..
	 * @param id  first level comment's talk id
	 * @return first level comment's second level comments
	 */
	public List<CommentLevel2> getCommentsById(int id) {
		List<CommentLevel2> comments = new ArrayList<CommentLevel2>();
		Connection conn = DB.getConn();
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

		String sql = "select * from SecondLevelComments where talkId=? ";
		PreparedStatement pstmt = DB.prepare(conn, sql);
		try {
			UserService userService = UserService.getInstance();
			int userId = 0;

			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				CommentLevel2 comment = getCommentFromRs(rs);

				// Here we should get the user's info and set it into comment.
				userId = comment.getUser().getUserId();
				comment.setUser(userService.getUserForCommentById(userId));

				comments.add(comment);
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DB.close(pstmt);
			DB.close(conn);
		}

		return comments;
	}

	private CommentLevel2 getCommentFromRs(ResultSet rs) {
		CommentLevel2 comment = new CommentLevel2();
		try {
			comment.setCommentId(rs.getInt("commentId"));
			comment.setContent(rs.getString("content"));
			comment.setPostTime(new Date(rs.getTimestamp("postTime").getTime()));
			User user = new User();
			user.setUserId(rs.getInt("userId"));
			comment.setUser(user);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return comment;
	}
}
